package zy.dao.shop.kpiassess.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.kpiassess.KpiAssessDAO;
import zy.entity.base.emp.T_Base_EmpGroupList;
import zy.entity.shop.kpiassess.T_Shop_KpiAssess;
import zy.entity.shop.kpiassess.T_Shop_KpiAssessList;
import zy.entity.shop.kpiassess.T_Shop_KpiAssessReward;
import zy.entity.sys.kpi.T_Sys_KpiScore;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class KpiAssessDAOImpl extends BaseDaoImpl implements KpiAssessDAO{
	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_kpiassess t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ka_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND ka_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND ka_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_type"))) {
			sql.append(" AND ka_type = :ka_type ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_state"))) {
			sql.append(" AND ka_state = :ka_state ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_shop_code"))) {
			sql.append(" AND ka_shop_code = :ka_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_number"))) {
			sql.append(" AND INSTR(ka_number,:ka_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Shop_KpiAssess> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ka_id,ka_number,ka_type,ka_begin,ka_end,ka_remark,ka_state,ka_summary,ka_sysdate,ka_shop_code,ka_us_id,t.companyid, ");
		sql.append(" (SELECT GROUP_CONCAT(DISTINCT kal_name) FROM t_shop_kpiassesslist kal WHERE kal_number = ka_number AND kal.companyid = t.companyid) AS kal_name");
		sql.append(" FROM t_shop_kpiassess t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ka_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND ka_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND ka_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_type"))) {
			sql.append(" AND ka_type = :ka_type ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_state"))) {
			sql.append(" AND ka_state = :ka_state ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_shop_code"))) {
			sql.append(" AND ka_shop_code = :ka_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("ka_number"))) {
			sql.append(" AND INSTR(ka_number,:ka_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY ka_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_KpiAssess.class));
	}
	
	@Override
	public List<T_Shop_KpiAssess> list4cashier(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ka_id,t.ka_number,ka_type,ka_begin,ka_end,ka_remark,ka_state,ka_summary,ka_sysdate,ka_shop_code,ka_us_id,t.companyid, ");
		sql.append(" (SELECT GROUP_CONCAT(DISTINCT kal_name) FROM t_shop_kpiassesslist kal WHERE kal_number = t.ka_number AND kal.companyid = t.companyid) AS kal_name");
		sql.append(" FROM t_shop_kpiassess t");
		sql.append(" JOIN(");
		sql.append(" SELECT DISTINCT ka_number,ka.companyid");
		sql.append(" FROM t_shop_kpiassess ka");
		sql.append(" JOIN t_shop_kpiassesslist kal ON ka.ka_number = kal.kal_number AND ka.companyid = kal.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND kal_code = :shop_code");
		sql.append(" AND ka_type = 0");
		sql.append(" AND ka_state = 0");
		sql.append(" AND ka_begin <= NOW()");
		sql.append(" AND ka_end >= NOW()");
		sql.append(" AND ka.companyid = :companyid");
		sql.append(" UNION ALL");
		sql.append(" SELECT DISTINCT ka_number,ka.companyid");
		sql.append(" FROM t_shop_kpiassess ka");
		sql.append(" JOIN t_shop_kpiassesslist kal ON ka.ka_number = kal.kal_number AND ka.companyid = kal.companyid");
		sql.append(" JOIN t_base_empgroup eg ON eg.eg_code = kal_code AND eg.companyid = kal.companyid");
		sql.append(" JOIN t_base_empgrouplist egl ON egl.egl_eg_code = eg.eg_code AND egl.companyid = eg.companyid");
		sql.append(" JOIN t_base_emp em ON egl.egl_em_code = em.em_code AND em.companyid = egl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND em_shop_code = :shop_code");
		sql.append(" AND ka_type = 1");
		sql.append(" AND ka_state = 0");
		sql.append(" AND ka_begin <= NOW()");
		sql.append(" AND ka_end >= NOW()");
		sql.append(" AND ka.companyid = :companyid");
		sql.append(" UNION ALL");
		sql.append(" SELECT DISTINCT ka_number,ka.companyid");
		sql.append(" FROM t_shop_kpiassess ka");
		sql.append(" JOIN t_shop_kpiassesslist kal ON ka.ka_number = kal.kal_number AND ka.companyid = kal.companyid");
		sql.append(" JOIN t_base_emp em ON kal_code = em_code AND em.companyid = kal.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND em_shop_code = :shop_code");
		sql.append(" AND ka_type = 2");
		sql.append(" AND ka_state = 0");
		sql.append(" AND ka_begin <= NOW()");
		sql.append(" AND ka_end >= NOW()");
		sql.append(" AND ka.companyid = :companyid");
		sql.append(" ) temp ON temp.ka_number = t.ka_number AND temp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("ka_type"))) {
			sql.append(" AND ka_type = :ka_type ");
		}
		sql.append(" AND ka_state = 0");
		sql.append(" AND t.companyid =:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY ka_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_KpiAssess.class));
	}
	
	@Override
	public T_Shop_KpiAssess load(Integer ka_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ka_id,ka_number,ka_type,ka_begin,ka_end,ka_remark,ka_state,ka_summary,ka_sysdate,ka_shop_code,ka_us_id,t.companyid ");
		sql.append(" FROM t_shop_kpiassess t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ka_id = :ka_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ka_id", ka_id),
					new BeanPropertyRowMapper<>(T_Shop_KpiAssess.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_KpiAssess load(String ka_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ka_id,ka_number,ka_type,ka_begin,ka_end,ka_remark,ka_state,ka_summary,ka_sysdate,ka_shop_code,ka_us_id,t.companyid ");
		sql.append(" FROM t_shop_kpiassess t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ka_number = :ka_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ka_number", ka_number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_KpiAssess.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<Map<String, Object>> loadHeader(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT kal_code,kal_name");
		sql.append(" FROM t_shop_kpiassesslist");
		sql.append(" WHERE 1=1");
		sql.append(" AND kal_number = :number");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY kal_code");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Shop_KpiAssessList> loadDetail(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT kal_id,kal_number,kal_code,kal_name,kal_ki_code,kal_complete,kal_score,companyid,");
		sql.append(" (SELECT ki_name FROM common_kpi ki WHERE ki_code = kal_ki_code LIMIT 1) AS ki_name,");
		sql.append(" (SELECT CONCAT(GROUP_CONCAT(kar_rw_code),'#',GROUP_CONCAT(rw_name)) ");
		sql.append(" FROM t_shop_kpiassessreward kar");
		sql.append(" JOIN t_sys_reward rw ON rw_code = kar_rw_code AND rw.companyid = kar.companyid");
		sql.append(" WHERE kar_number = kal_number AND kar_kal_code = kal_code AND kar_ki_code = kal_ki_code AND kar.companyid = t.companyid) AS reward_codes");
		sql.append(" FROM t_shop_kpiassesslist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND kal_number = :number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY kal_code,kal_ki_code");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Shop_KpiAssessList.class));
	}
	
	@Override
	public List<T_Shop_KpiAssessList> statDetail(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Integer ka_type = (Integer)params.get("ka_type");
		Integer companyid = (Integer)params.get(CommonUtil.COMPANYID);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT kal_id,kal_code,kal_name,kal_ki_code,ki_name,ki_identity");
		sql.append(" FROM t_shop_kpiassesslist t");
		sql.append(" JOIN common_kpi ki ON ki_code = kal_ki_code");
		sql.append(" WHERE kal_number = :number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY kal_code,kal_ki_code");
		List<T_Shop_KpiAssessList> kpiAssessLists = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_KpiAssessList.class));
		Set<String> kiCodeSet = new HashSet<String>();
		Set<String> codeSet = new HashSet<String>();
		Set<String> kpiIdentitySet = new HashSet<String>();
		for (T_Shop_KpiAssessList item : kpiAssessLists) {
			kiCodeSet.add(item.getKal_ki_code());
			codeSet.add(item.getKal_code());
			kpiIdentitySet.add(item.getKi_identity());
		}
		List<String> shopCodes = new ArrayList<String>();
		List<String> empCodes = new ArrayList<String>();
		Map<String, String> emp_shop = new HashMap<String, String>();
		Map<String, List<String>> group_shop = new HashMap<String, List<String>>();//员工组员工所属店铺集合
		Map<String, List<String>> group_emp = new HashMap<String, List<String>>();//员工组员工集合
		if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
			shopCodes.addAll(codeSet);
		}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
			sql.setLength(0);
			sql.append(" SELECT DISTINCT eg_code,em_code,em_shop_code");
			sql.append(" FROM t_base_empgroup t");
			sql.append(" JOIN t_base_empgrouplist egl ON egl_eg_code = eg_code AND egl.companyid = t.companyid");
			sql.append(" JOIN t_base_emp em ON em_code = egl_em_code AND em.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND eg_code IN(:eg_codes)");
			sql.append(" AND t.companyid = :companyid");
			List<Map<String, Object>> temps = namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("eg_codes", new ArrayList<String>(codeSet)).addValue("companyid", companyid));
			for (Map<String, Object> item : temps) {
				emp_shop.put(StringUtil.trimString(item.get("em_code")), StringUtil.trimString(item.get("em_shop_code")));
				if(group_shop.containsKey(StringUtil.trimString(item.get("eg_code")))){
					if(!group_shop.get(StringUtil.trimString(item.get("eg_code"))).contains(StringUtil.trimString(item.get("em_shop_code")))){
						group_shop.get(StringUtil.trimString(item.get("eg_code"))).add(StringUtil.trimString(item.get("em_shop_code")));
					}
				}else {
					group_shop.put(StringUtil.trimString(item.get("eg_code")), new ArrayList<String>());
					group_shop.get(StringUtil.trimString(item.get("eg_code"))).add(StringUtil.trimString(item.get("em_shop_code")));
				}
				if(group_emp.containsKey(StringUtil.trimString(item.get("eg_code")))){
					if(!group_emp.get(StringUtil.trimString(item.get("eg_code"))).contains(StringUtil.trimString(item.get("em_code")))){
						group_emp.get(StringUtil.trimString(item.get("eg_code"))).add(StringUtil.trimString(item.get("em_code")));
					}
				}else {
					group_emp.put(StringUtil.trimString(item.get("eg_code")), new ArrayList<String>());
					group_emp.get(StringUtil.trimString(item.get("eg_code"))).add(StringUtil.trimString(item.get("em_code")));
				}
			}
			for(String key:emp_shop.keySet()){
				empCodes.add(key);
				if(!shopCodes.contains(emp_shop.get(key))){
					shopCodes.add(emp_shop.get(key));
				}
			}
		}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
			empCodes.addAll(codeSet);
			sql.setLength(0);
			sql.append(" SELECT em_code,em_shop_code");
			sql.append(" FROM t_base_emp t");
			sql.append(" WHERE 1=1");
			sql.append(" AND em_code IN(:em_codes)");
			sql.append(" AND t.companyid = :companyid");
			List<Map<String, Object>> temps = namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("em_codes", empCodes).addValue("companyid", companyid));
			for (Map<String, Object> item : temps) {
				emp_shop.put(StringUtil.trimString(item.get("em_code")), StringUtil.trimString(item.get("em_shop_code")));
				if(!shopCodes.contains(StringUtil.trimString(item.get("em_shop_code")))){
					shopCodes.add(StringUtil.trimString(item.get("em_shop_code")));
				}
			}
		}
		params.put("shopCodes", shopCodes);
		params.put("empCodes", empCodes);
//		基础指标(相关指标)
//		进店量：进店量、接待率、试穿率
//		销售数量：销售数量、连带率、平均单价
//		销售额：销售额、平均折扣率、平均单价、客单价
//		成交单数：成交率、连带率、客单价
		final Map<String, Integer> comeAmount_Base = new HashMap<String, Integer>();//key:店铺编号
		final Map<String, Integer> sellAmount_Base = new HashMap<String, Integer>();//key:店铺编号或员工编号
		final Map<String, Double> sellMoney_Base = new HashMap<String, Double>();//key:店铺编号或员工编号
		final Map<String, Double> retailMoney_Base = new HashMap<String, Double>();//key:店铺编号或员工编号
		final Map<String, Integer> receiveAmount_Base = new HashMap<String, Integer>();//key:店铺编号或员工编号
		final Map<String, Integer> dealCount_Sell_Base = new HashMap<String, Integer>();//key:店铺编号或员工编号
		final Map<String, Integer> dealCount_Back_Base = new HashMap<String, Integer>();//key:店铺编号或员工编号
		final Map<String, Integer> newVipCount_Base = new HashMap<String, Integer>();//key:店铺编号或员工编号
		if(kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_COMEAMOUNT)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_RECEIVE_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_TRY_RATE)){//查询进店量
			sql.setLength(0);
			sql.append(" SELECT da_shop_code,SUM(da_come) AS comeAmount");
			sql.append(" FROM t_sell_day");
			sql.append(" WHERE 1=1");
			sql.append(" AND da_date >= :begin");
			sql.append(" AND da_date <= :end");
			sql.append(" AND da_shop_code IN(:shopCodes)");
			sql.append(" AND companyid = :companyid");
			sql.append(" GROUP BY da_shop_code");
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					comeAmount_Base.put(rs.getString("da_shop_code"), rs.getInt("comeAmount"));
					return null;
				};
			});
		}
		if(kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_SELLAMOUNT)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_JOINT_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_AVG_PRICE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_SELLMONEY)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_AVG_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_AVG_PRICE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_AVG_SELLPRICE)){//查询销售数量、销售额
			sql.setLength(0);
			sql.append(" SELECT ");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" shl_shop_code AS code,");
			}else {
				sql.append(" shl_main AS code,");
			}
			sql.append(" IFNULL(SUM(shl_amount),0) AS sellAmount,");
			sql.append(" IFNULL(SUM(shl_money),0) AS sellMoney,");
			sql.append(" IFNULL(SUM(shl_sell_price*shl_amount),0) AS retailMoney");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" WHERE 1=1");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" AND shl_shop_code IN(:shopCodes)");
			}else {
				sql.append(" AND shl_main IN(:empCodes)");
			}
			sql.append(" AND shl_date >= :begin");
			sql.append(" AND shl_date <= :end");
			sql.append(" AND t.companyid = :companyid");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" GROUP BY shl_shop_code");
			}else {
				sql.append(" GROUP BY shl_main");
			}
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					sellAmount_Base.put(rs.getString("code"), rs.getInt("sellAmount"));
					sellMoney_Base.put(rs.getString("code"), rs.getDouble("sellMoney"));
					retailMoney_Base.put(rs.getString("code"), rs.getDouble("retailMoney"));
					return null;
				};
			});
		}
		if(kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_RECEIVE_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_DEAL_RATE)){//查询接待人数
			sql.setLength(0);
			sql.append(" SELECT ");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" re_shop_code AS code,");
			}else {
				sql.append(" re_em_code AS code,");
			}
			sql.append(" COUNT(1) AS receiveAmount");
			sql.append(" FROM t_sell_receive t");
			sql.append(" WHERE 1=1");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" AND re_shop_code IN(:shopCodes)");
			}else {
				sql.append(" AND re_em_code IN(:empCodes)");
			}
			sql.append(" AND re_date >= :begin");
			sql.append(" AND re_date <= :end");
			sql.append(" AND t.companyid = :companyid");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" GROUP BY re_shop_code");
			}else {
				sql.append(" GROUP BY re_em_code");
			}
			
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					receiveAmount_Base.put(rs.getString("code"), rs.getInt("receiveAmount"));
					return null;
				};
			});
		}
		if(kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_DEAL_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_JOINT_RATE)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_AVG_SELLPRICE)){//查询成交单数
			sql.setLength(0);
			sql.append(" SELECT ");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" shl_shop_code AS code,");
			}else {
				sql.append(" shl_main AS code,");
			}
			sql.append(" shl_state,COUNT(DISTINCT shl_number) AS dealCount");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" WHERE 1=1");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" AND shl_shop_code IN(:shopCodes)");
			}else {
				sql.append(" AND shl_main IN(:empCodes)");
			}
			sql.append(" AND shl_date >= :begin");
			sql.append(" AND shl_date <= :end");
			sql.append(" AND shl_state IN(0,1)");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY shl_state");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" ,shl_shop_code");
			}else {
				sql.append(" ,shl_main");
			}
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					if("0".equals(rs.getString("shl_state"))){
						dealCount_Sell_Base.put(rs.getString("code"), rs.getInt("dealCount"));
					}else {
						dealCount_Back_Base.put(rs.getString("code"), rs.getInt("dealCount"));
					}
					return null;
				};
			});
		}
		if(kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_NEWVIP_COUNT)
				||kpiIdentitySet.contains(CommonUtil.KPI_IDENTITY_VIPCONTRIBUTION_RATE)){//查询新增会员
			sql.setLength(0);
			sql.append(" SELECT");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" vm_shop_code AS code,");
			}else {
				sql.append(" vm_manager_code AS code,");
			}
			sql.append(" COUNT(1) AS newVipCount");
			sql.append(" FROM t_vip_member");
			sql.append(" WHERE 1=1");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" AND vm_shop_code IN(:shopCodes)");
			}else {
				sql.append(" AND vm_manager_code IN(:empCodes)");
			}
			sql.append(" AND vm_sysdate >= :begin");
			sql.append(" AND vm_sysdate <= :end");
			sql.append(" AND companyid = :companyid");
			if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
				sql.append(" GROUP BY vm_shop_code");
			}else {
				sql.append(" GROUP BY vm_manager_code");
			}
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					newVipCount_Base.put(rs.getString("code"), rs.getInt("newVipCount"));
					return null;
				};
			});
		}
		
		Map<String, Map<String, Object>> kpiResultMap = new HashMap<String, Map<String,Object>>();
		Map<String, Object> item = null;
		for (String identity : kpiIdentitySet) {
			item = new HashMap<String, Object>();
			if(CommonUtil.KPI_IDENTITY_COMEAMOUNT.equals(identity)){//进店量
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int amount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(comeAmount_Base.containsKey(key)){
							amount = comeAmount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_shop.get(key);//员工组员工所属的店铺编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(comeAmount_Base.containsKey(code)){
									amount += comeAmount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(comeAmount_Base.containsKey(emp_shop.get(key))){
							amount = comeAmount_Base.get(emp_shop.get(key));
						}
					}
					item.put(key, amount);
				}
			}else if(CommonUtil.KPI_IDENTITY_SELLMONEY.equals(identity)){//销售额
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					double money = 0d;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							money = sellMoney_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellMoney_Base.containsKey(code)){
									money += sellMoney_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							money = sellMoney_Base.get(key);
						}
					}
					item.put(key, String.format("%.2f", money));
				}
			}else if(CommonUtil.KPI_IDENTITY_SELLAMOUNT.equals(identity)){//销售数量
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int amount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellAmount_Base.containsKey(key)){
							amount = sellAmount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellAmount_Base.containsKey(code)){
									amount += sellAmount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellAmount_Base.containsKey(key)){
							amount = sellAmount_Base.get(key);
						}
					}
					item.put(key, amount);
				}
			}else if(CommonUtil.KPI_IDENTITY_RECEIVE_RATE.equals(identity)){//接待率
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int receiveamount = 0;
					int comeAmount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(receiveAmount_Base.containsKey(key)){
							receiveamount = receiveAmount_Base.get(key);
						}
						if(comeAmount_Base.containsKey(key)){
							comeAmount = comeAmount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(receiveAmount_Base.containsKey(code)){
									receiveamount += receiveAmount_Base.get(code);
								}
							}
						}
						List<String> temp2 = group_shop.get(key);//员工组员工所属的店铺编号
						if (temp2 != null && temp2.size() > 0) {
							for (String code : temp2) {
								if(comeAmount_Base.containsKey(code)){
									comeAmount += comeAmount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(receiveAmount_Base.containsKey(key)){
							receiveamount = receiveAmount_Base.get(key);
						}
						if(comeAmount_Base.containsKey(emp_shop.get(key))){
							comeAmount = comeAmount_Base.get(emp_shop.get(key));
						}
					}
					if (comeAmount != 0) {
						item.put(key, String.format("%.2f", receiveamount/(double)comeAmount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_TRY_RATE.equals(identity)){//试穿率
				final Map<String, Integer> tryAmountMap = new HashMap<String, Integer>();
				sql.setLength(0);
				sql.append(" SELECT ");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" tr_shop_code AS code,");
				}else {
					sql.append(" tr_em_code AS code,");
				}
				sql.append(" COUNT(1) AS tryAmount");
				sql.append(" FROM t_sell_try t");
				sql.append(" WHERE 1=1");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" AND tr_shop_code IN(:shopCodes)");
				}else {
					sql.append(" AND tr_em_code IN(:empCodes)");
				}
				sql.append(" AND tr_date >= :begin");
				sql.append(" AND tr_date <= :end");
				sql.append(" AND t.companyid = :companyid");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" GROUP BY tr_shop_code");
				}else {
					sql.append(" GROUP BY tr_em_code");
				}
				namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
					@Override
					public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
						tryAmountMap.put(rs.getString("code"), rs.getInt("tryAmount"));
						return null;
					};
				});
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int tryamount = 0;
					int comeAmount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(tryAmountMap.containsKey(key)){
							tryamount = tryAmountMap.get(key);
						}
						if(comeAmount_Base.containsKey(key)){
							comeAmount = comeAmount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(tryAmountMap.containsKey(code)){
									tryamount += tryAmountMap.get(code);
								}
							}
						}
						List<String> temp2 = group_shop.get(key);//员工组员工所属的店铺编号
						if (temp2 != null && temp2.size() > 0) {
							for (String code : temp2) {
								if(comeAmount_Base.containsKey(code)){
									comeAmount += comeAmount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(tryAmountMap.containsKey(key)){
							tryamount = tryAmountMap.get(key);
						}
						if(comeAmount_Base.containsKey(emp_shop.get(key))){
							comeAmount = comeAmount_Base.get(emp_shop.get(key));
						}
					}
					if (comeAmount != 0) {
						item.put(key, String.format("%.2f", tryamount/(double)comeAmount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_DEAL_RATE.equals(identity)){//成交率=成交单数/接待人数
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int receiveamount = 0;
					int dealCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(receiveAmount_Base.containsKey(key)){
							receiveamount = receiveAmount_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(receiveAmount_Base.containsKey(code)){
									receiveamount += receiveAmount_Base.get(code);
								}
								if(dealCount_Sell_Base.containsKey(code)){
									dealCount += dealCount_Sell_Base.get(code);
								}
								if(dealCount_Back_Base.containsKey(code)){
									dealCount -= dealCount_Back_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(receiveAmount_Base.containsKey(key)){
							receiveamount = receiveAmount_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}
					if (receiveamount != 0) {
						item.put(key, String.format("%.2f", dealCount/(double)receiveamount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_JOINT_RATE.equals(identity)){//连带率=销售数量/成交单数
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int sellAmount = 0;
					int dealCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellAmount_Base.containsKey(key)){
							sellAmount = sellAmount_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellAmount_Base.containsKey(code)){
									sellAmount += sellAmount_Base.get(code);
								}
								if(dealCount_Sell_Base.containsKey(code)){
									dealCount += dealCount_Sell_Base.get(code);
								}
								if(dealCount_Back_Base.containsKey(code)){
									dealCount -= dealCount_Back_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellAmount_Base.containsKey(key)){
							sellAmount = sellAmount_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}
					if (dealCount != 0) {
						item.put(key, String.format("%.2f", sellAmount/(double)dealCount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_RETURN_RATE.equals(identity)){//退单率=退单数/销售单数
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int dealCount_Sell = 0;
					int dealCount_Back = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount_Sell += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount_Back += dealCount_Back_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(dealCount_Sell_Base.containsKey(code)){
									dealCount_Sell += dealCount_Sell_Base.get(code);
								}
								if(dealCount_Back_Base.containsKey(code)){
									dealCount_Back += dealCount_Back_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount_Sell += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount_Back += dealCount_Back_Base.get(key);
						}
					}
					if (dealCount_Sell != 0) {
						item.put(key, String.format("%.2f", dealCount_Back/(double)dealCount_Sell));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_NEWVIP_COUNT.equals(identity)){//新增会员
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int newVipCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(newVipCount_Base.containsKey(key)){
							newVipCount = newVipCount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(newVipCount_Base.containsKey(code)){
									newVipCount += newVipCount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(newVipCount_Base.containsKey(key)){
							newVipCount = newVipCount_Base.get(key);
						}
					}
					item.put(key, newVipCount);
				}
			}else if(CommonUtil.KPI_IDENTITY_VIPCONTRIBUTION_RATE.equals(identity)){//VIP贡献率
				sql.setLength(0);
				sql.append(" SELECT sp_code");
				sql.append(" FROM t_base_shop t");
				if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
					sql.append(getShopSQL(shop_type, 0));
					sql.append(" WHERE 1 = 1");
				}else{//自营、加盟、合伙
					sql.append(" WHERE 1 = 1");
					sql.append(" AND sp_code = :shop_code");
				}
				sql.append(" AND t.companyid=:companyid");
				List<String> allShopCodes = namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
				params.put("allShopCodes", allShopCodes);
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_vip_member");
				sql.append(" WHERE 1=1");
				sql.append(" AND vm_shop_code IN(:allShopCodes)");
				sql.append(" AND vm_sysdate >= :begin");
				sql.append(" AND vm_sysdate <= :end");
				sql.append(" AND companyid = :companyid");
				int allNewVipCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int newVipCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(newVipCount_Base.containsKey(key)){
							newVipCount = newVipCount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(newVipCount_Base.containsKey(code)){
									newVipCount += newVipCount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(newVipCount_Base.containsKey(key)){
							newVipCount = newVipCount_Base.get(key);
						}
					}
					if (allNewVipCount != 0) {
						item.put(key, String.format("%.2f", newVipCount/(double)allNewVipCount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_AVG_SELLMONEY.equals(identity)){//人均销售=销售总额/员工数
				final Map<String, Integer> empCountMap = new HashMap<String, Integer>();
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){//查询店铺员工个数
					sql.setLength(0);
					sql.append(" SELECT em_shop_code,COUNT(1) AS empCount");
					sql.append(" FROM t_base_emp t");
					sql.append(" WHERE 1=1");
					sql.append(" AND em_state = 0");
					sql.append(" AND em_shop_code IN(:shopCodes)");
					sql.append(" AND t.companyid = :companyid");
					sql.append(" GROUP BY em_shop_code");
					namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
						@Override
						public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
							empCountMap.put(rs.getString("em_shop_code"), rs.getInt("empCount"));
							return null;
						};
					});
				}
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					double allSellMoney = 0;
					int empCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							allSellMoney = sellMoney_Base.get(key);
						}
						if(empCountMap.containsKey(key)){
							empCount = empCountMap.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							empCount = temp.size();
							for (String code : temp) {
								if(sellMoney_Base.containsKey(code)){
									allSellMoney += sellMoney_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							allSellMoney = sellMoney_Base.get(key);
						}
						empCount = 1;
					}
					if (empCount != 0) {
						item.put(key, String.format("%.2f", allSellMoney/(double)empCount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_AVG_RATE.equals(identity)){//平均折扣率=销售金额/零售金额
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					double sellMoney = 0d;
					double retailMoney = 0d;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(retailMoney_Base.containsKey(key)){
							retailMoney = retailMoney_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellMoney_Base.containsKey(code)){
									sellMoney += sellMoney_Base.get(code);
								}
								if(retailMoney_Base.containsKey(code)){
									retailMoney += retailMoney_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(retailMoney_Base.containsKey(key)){
							retailMoney = retailMoney_Base.get(key);
						}
					}
					if (retailMoney != 0) {
						item.put(key, String.format("%.2f", sellMoney/retailMoney));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_AVG_PRICE.equals(identity)){//平均单价=销售额/销售数量
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					double sellMoney = 0d;
					int sellAmount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(sellAmount_Base.containsKey(key)){
							sellAmount = sellAmount_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellMoney_Base.containsKey(code)){
									sellMoney += sellMoney_Base.get(code);
								}
								if(sellAmount_Base.containsKey(code)){
									sellAmount += sellAmount_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(sellAmount_Base.containsKey(key)){
							sellAmount = sellAmount_Base.get(key);
						}
					}
					if (sellAmount != 0) {
						item.put(key, String.format("%.2f", sellMoney/sellAmount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_AVG_SELLPRICE.equals(identity)){//客单价=销售额/成交单数
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					double sellMoney = 0d;
					int dealCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(sellMoney_Base.containsKey(code)){
									sellMoney += sellMoney_Base.get(code);
								}
								if(dealCount_Sell_Base.containsKey(code)){
									dealCount += dealCount_Sell_Base.get(code);
								}
								if(dealCount_Back_Base.containsKey(code)){
									dealCount -= dealCount_Back_Base.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(sellMoney_Base.containsKey(key)){
							sellMoney = sellMoney_Base.get(key);
						}
						if(dealCount_Sell_Base.containsKey(key)){
							dealCount += dealCount_Sell_Base.get(key);
						}
						if(dealCount_Back_Base.containsKey(key)){
							dealCount -= dealCount_Back_Base.get(key);
						}
					}
					if (dealCount != 0) {
						item.put(key, String.format("%.2f", sellMoney/dealCount));
					}else{
						item.put(key, 0d);
					}
				}
			}else if(CommonUtil.KPI_IDENTITY_BACK_RATE.equals(identity)){//回购率=会员成交单数/会员总数
				final Map<String, Integer> vipDealCountMap = new HashMap<String, Integer>();
				sql.setLength(0);
				sql.append(" SELECT ");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" shl_shop_code AS code,");
				}else {
					sql.append(" shl_main AS code,");
				}
				sql.append(" COUNT(DISTINCT shl_number) AS dealVipCount");
				sql.append(" FROM t_sell_shoplist t");
				sql.append(" WHERE 1=1");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" AND shl_shop_code IN(:shopCodes)");
				}else {
					sql.append(" AND shl_main IN(:empCodes)");
				}
				sql.append(" AND shl_date >= :begin");
				sql.append(" AND shl_date <= :end");
				sql.append(" AND shl_state IN(0,1)");
				sql.append(" AND shl_vip_code != ''");
				sql.append(" AND shl_vip_code IS NOT NULL");
				sql.append(" AND t.companyid = :companyid");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" GROUP BY shl_shop_code");
				}else {
					sql.append(" GROUP BY shl_main");
				}
				namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
					@Override
					public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
						vipDealCountMap.put(rs.getString("code"), rs.getInt("dealVipCount"));
						return null;
					};
				});
				final Map<String, Integer> vipCountMap = new HashMap<String, Integer>();
				sql.setLength(0);
				sql.append(" SELECT");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" vm_shop_code AS code,");
				}else {
					sql.append(" vm_manager_code AS code,");
				}
				sql.append(" COUNT(1) AS vipCount");
				sql.append(" FROM t_vip_member");
				sql.append(" WHERE 1=1");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" AND vm_shop_code IN(:shopCodes)");
				}else {
					sql.append(" AND vm_manager_code IN(:empCodes)");
				}
				sql.append(" AND companyid = :companyid");
				if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
					sql.append(" GROUP BY vm_shop_code");
				}else {
					sql.append(" GROUP BY vm_manager_code");
				}
				namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
					@Override
					public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
						vipCountMap.put(rs.getString("code"), rs.getInt("vipCount"));
						return null;
					};
				});
				for (String key : codeSet) {//key为店铺编号、员工组编号、员工编号
					int vipDealCount = 0;
					int vipCount = 0;
					if(CommonUtil.KPI_ASSESS_TYPE_SHOP.equals(ka_type)){
						if(vipDealCountMap.containsKey(key)){
							vipDealCount = vipDealCountMap.get(key);
						}
						if(vipCountMap.containsKey(key)){
							vipCount = vipCountMap.get(key);
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMPGROUP.equals(ka_type)){
						List<String> temp = group_emp.get(key);//员工组员工编号
						if (temp != null && temp.size() > 0) {
							for (String code : temp) {
								if(vipDealCountMap.containsKey(code)){
									vipDealCount += vipDealCountMap.get(code);
								}
								if(vipCountMap.containsKey(code)){
									vipCount += vipCountMap.get(code);
								}
							}
						}
					}else if(CommonUtil.KPI_ASSESS_TYPE_EMP.equals(ka_type)){
						if(vipDealCountMap.containsKey(key)){
							vipDealCount = vipDealCountMap.get(key);
						}
						if(vipCountMap.containsKey(key)){
							vipCount = vipCountMap.get(key);
						}
					}
					if (vipCount != 0) {
						item.put(key, String.format("%.2f", vipDealCount/(double)vipCount));
					}else{
						item.put(key, 0d);
					}
				}
			}
			kpiResultMap.put(identity, item);
		}
		//遍历实际完成情况
		for (T_Shop_KpiAssessList kpiAssessList : kpiAssessLists) {
			Map<String, Object> completeMap = kpiResultMap.get(kpiAssessList.getKi_identity());
			kpiAssessList.setKal_complete(StringUtil.trimString(completeMap.get(kpiAssessList.getKal_code())));
		}
		sql.setLength(0);
		MapSqlParameterSource parameterSource = new MapSqlParameterSource().addValue("companyid", companyid);
		parameterSource.addValue("kiCodes", new ArrayList<String>(kiCodeSet));
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟
			parameterSource.addValue("shop_code", StringUtil.trimString(params.get(CommonUtil.SHOP_CODE)));
		}else{//自营、合伙
			parameterSource.addValue("shop_code", StringUtil.trimString(params.get(CommonUtil.SHOP_UPCODE)));
		}
		sql.append(" SELECT ks_id,ks_ki_code,ks_min,ks_max,ks_score,ks_shop_code,ks_rw_code,companyid,");
		sql.append(" (SELECT rw_name FROM t_sys_reward rw WHERE rw_code = ks_rw_code AND rw.companyid = t.companyid LIMIT 1) AS reward_name");
		sql.append(" FROM t_sys_kpiscore t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ks_ki_code IN(:kiCodes)");
		sql.append(" AND ks_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY ks_ki_code,ks_min");
		List<T_Sys_KpiScore> kpiScores = namedParameterJdbcTemplate.query(sql.toString(), parameterSource, new BeanPropertyRowMapper<>(T_Sys_KpiScore.class));
		Map<String, List<T_Sys_KpiScore>> kpiScoreMap = new HashMap<String, List<T_Sys_KpiScore>>();
		for (T_Sys_KpiScore score : kpiScores) {
			if(!kpiScoreMap.containsKey(score.getKs_ki_code())){
				kpiScoreMap.put(score.getKs_ki_code(), new ArrayList<T_Sys_KpiScore>());
			}
			kpiScoreMap.get(score.getKs_ki_code()).add(score);
		}
		//根据指标完成情况，遍历获得分数及奖励
		for (T_Shop_KpiAssessList kpiAssessList : kpiAssessLists) {
			List<T_Sys_KpiScore> scores = kpiScoreMap.get(kpiAssessList.getKal_ki_code());
			int kal_score = 0;
			String rw_code = "";
			String rw_name = "";
			if (scores != null && scores.size() > 0) {
				double complete = Double.parseDouble(kpiAssessList.getKal_complete());
				if(complete<scores.get(0).getKs_min()){//小于最小值
					kal_score = 0;
				} else if (complete >= scores.get(scores.size() - 1).getKs_max()) {//大于最大值
					kal_score = scores.get(scores.size()-1).getKs_score();
					rw_code = scores.get(scores.size()-1).getKs_rw_code();
					rw_name = scores.get(scores.size()-1).getReward_name();
				}else {
					for (T_Sys_KpiScore score : scores) {
						if (complete >= score.getKs_min() && complete < score.getKs_max()) {// 区间判断规则：左闭右开
							kal_score = score.getKs_score();
							rw_code = score.getKs_rw_code();
							rw_name = score.getReward_name();
						}
					}
				}
			}
			kpiAssessList.setKal_score(kal_score);
			kpiAssessList.setReward_codes(StringUtil.trimString(rw_code));
			kpiAssessList.setReward_names(StringUtil.trimString(rw_name));
		}
		return kpiAssessLists;
	}
	
	@Override
	public void save(T_Shop_KpiAssess kpiAssess,List<T_Shop_KpiAssessList> kpiAssessLists) {
		String prefix = CommonUtil.NUMBER_PREFIX_SHOP_KPIASSESS + DateUtil.getYearMonthDateYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ka_number))) AS new_number");
		sql.append(" FROM t_shop_kpiassess");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ka_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", kpiAssess.getCompanyid()), String.class);
		kpiAssess.setKa_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_kpiassess");
		sql.append(" (ka_number,ka_type,ka_begin,ka_end,ka_remark,ka_state,ka_summary,ka_sysdate,ka_shop_code,ka_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ka_number,:ka_type,:ka_begin,:ka_end,:ka_remark,:ka_state,:ka_summary,:ka_sysdate,:ka_shop_code,:ka_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(kpiAssess),holder);
		kpiAssess.setKa_id(holder.getKey().intValue());
		for (T_Shop_KpiAssessList item : kpiAssessLists) {
			item.setKal_number(kpiAssess.getKa_number());
			item.setCompanyid(kpiAssess.getCompanyid());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_kpiassesslist");
		sql.append(" (kal_number,kal_code,kal_name,kal_ki_code,kal_complete,kal_score,companyid)");
		sql.append(" VALUES");
		sql.append(" (:kal_number,:kal_code,:kal_name,:kal_ki_code,:kal_complete,:kal_score,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(kpiAssessLists.toArray()));
	}
	
	@Override
	public void complete(T_Shop_KpiAssess kpiAssess) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_kpiassess");
		sql.append(" SET ka_state=:ka_state");
		sql.append(" ,ka_summary = :ka_summary");
		sql.append(" WHERE ka_id=:ka_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(kpiAssess));
	}
	
	@Override
	public void completeDetail(List<T_Shop_KpiAssessList> kpiAssessLists) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_kpiassesslist");
		sql.append(" SET kal_complete=:kal_complete");
		sql.append(" ,kal_score = :kal_score");
		sql.append(" WHERE kal_id=:kal_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(kpiAssessLists.toArray()));
	}
	
	@Override
	public List<T_Base_EmpGroupList> loadEmpByGroup(List<String> eg_codes,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT egl_eg_code,egl_em_code");
		sql.append(" FROM t_base_empgrouplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND egl_eg_code IN(:eg_codes)");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("eg_codes", eg_codes).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Base_EmpGroupList.class));
		
	}
	
	@Override
	public void saveReward(List<T_Shop_KpiAssessReward> rewards) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_kpiassessreward");
		sql.append(" (kar_number,kar_kal_code,kar_ki_code,kar_rw_code,companyid)");
		sql.append(" VALUES(:kar_number,:kar_kal_code,:kar_ki_code,:kar_rw_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(rewards.toArray()));
	}
	
	@Override
	public void del(String ka_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_kpiassess");
		sql.append(" WHERE ka_number=:ka_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ka_number", ka_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_kpiassesslist");
		sql.append(" WHERE kal_number=:ka_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ka_number", ka_number).addValue("companyid", companyid));
	}
	
}
